package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class GetMRItems
 */
//@WebServlet(description = "取得測試項目的內容", urlPatterns = { "/GetMRItems" })
public class GetMRItems extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetMRItems() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String strSQL = "SELECT tpid, mrid, module, mrdescription, createdate, onlinedate, sourceid, isMail, mrno, process, yymm, description, designpm, isFromImport, fromXML, isTCsubmitAble, isTCsubmitted, isTCreviewed, isTCaccepted, isTRsubmitAble, isTRsubmitted, isTRreviewed, isTRaccepted FROM activiti.tms_tpitem WHERE tpid=?";
		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String returnResult = "";
		String mrid ="";
		ArrayList<MRBody> mrBodys = new ArrayList<MRBody>();
		ArrayList<TR> trs = new ArrayList<TR>();
		int ver = 0;	//版次
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			rs = ps.executeQuery();
			while (rs.next()) {
				
				boolean isTCsubmitAble = rs.getInt("isTCsubmitAble")==0?false:true; 
				boolean isTRsubmitAble = rs.getInt("isTRsubmitAble")==0?false:true;
				boolean isTCsubmitted = rs.getInt("isTCsubmitted")==0?false:true;
				boolean isTRsubmitted = rs.getInt("isTRsubmitted")==0?false:true;
				boolean isTCreviewed = rs.getInt("isTCreviewed")==0?false:true;
				boolean isTRreviewed = rs.getInt("isTRreviewed")==0?false:true;
				boolean isTCaccepted = rs.getInt("isTCaccepted")==0?false:true;
				boolean isTRaccepted = rs.getInt("isTRaccepted")==0?false:true;
				
				
				MRBody mrBody = new MRBody();
				
				mrBody.setTpid(rs.getString("tpid"));
				
				/** 在此要進一步判斷下一個畫面上的按鈕，那些可按那些不可按 **/
				mrid = rs.getString("mrid");
				mrBody.setMrid(mrid);
				
				/** 是否可重新匯入 **/
				mrBody.setImportAble(rs.getString("isFromImport").equalsIgnoreCase("true")?true:false);
				mrBody.setTCsubmitAble(isTCsubmitAble);
				mrBody.setTCsubmitted(isTCsubmitted);
				mrBody.setTCreviewed(isTCreviewed);
				mrBody.setTCaccepted(isTCaccepted);
				mrBody.setTRsubmitAble(isTRsubmitAble);
				mrBody.setTRsubmitted(isTRsubmitted);
				mrBody.setTRreviewed(isTRreviewed);
				mrBody.setTRaccepted(isTRaccepted);
				mrBody.setFileID(rs.getString("fromXML"));
				mrBody.setModule(rs.getString("module"));
				mrBody.setMrdescription(rs.getString("mrdescription"));
				mrBody.setSourceid(rs.getString("sourceid"));
				mrBody.setOnlinedate(rs.getString("onlinedate"));
				mrBody.setProcess(rs.getString("process"));
				mrBody.setYymm(rs.getString("yymm"));
				mrBody.setDesc(rs.getString("description"));
				mrBody.setDesignpm(erList("designpm",tpid,mrid,conn,ps,rs));
				mrBody.setCreatedate(rs.getString("createdate"));
				mrBody.setTester(erList("tester",tpid,mrid,conn,ps,rs));
				mrBody.setPropose(erList("propose",tpid,mrid,conn,ps,rs));
				mrBody.setDesigner(erList("designer",tpid,mrid,conn,ps,rs));
				mrBody.setRequire(reqList(tpid,mrid,conn,ps,rs));
				
				//尚欠programmer
				
				/** 是否可送審案例 **/
				strSQL = "SELECT MAX(ver) FROM tms_mrtcmapping t2 WHERE tpid="+tpid+" AND mrid='"+mrid+"'";
				ps = conn.prepareStatement(strSQL);
				ps.clearParameters();
				
				rs2 = ps.executeQuery();
				rs2.next();
				ver = rs2.getInt(1);
				mrBody.setVer(ver);
				
				
				/** 如果有設定過案例與測試項目的配對 **/
				if (isTCsubmitAble) {	//有做過 案例配對設定
					/** 因為是利用 sn 來降冪排序，所以如果同一個版本，被提交了兩次，系統只會呈現後送出的那個版本 **/
					strSQL = "SELECT trSN, edition, comment, retenum, succnum, failnum, cantnum, waitnum, creator, creatorGroup, createdTime FROM activiti.tms_mrtr WHERE tpid = ? AND mrid = ? AND ver = ? ORDER BY sn DESC";
					ps = conn.prepareStatement(strSQL);
					ps.setInt(1, tpid);
					ps.setString(2, mrid);
					ps.setInt(3, ver);
					rs2 = ps.executeQuery();
					while (rs2.next()) {	//表示有找到先前登打的結果，而且因為可能有多個案例，所以要用 while loop
						TR tr = new TR();  
						tr.setTr(rs2.getInt("trSN")+"");	//測試結果
						tr.setEdition(rs2.getString("edition"));	//系統版本
						tr.setComment("");
						tr.setRetestNum(rs2.getInt("retenum"));	//重驗次數
						tr.setSuccNum(rs2.getInt("succnum"));	//成功次數
						tr.setFailNum(rs2.getInt("failnum"));	//失敗次數
						tr.setCantNum(rs2.getInt("cantnum"));	//無法測試數
						tr.setWaitNum(rs2.getInt("waitnum"));	//待測試數
						tr.setCreator(rs2.getString("creator"));	//創建人員
						tr.setCreatorGroup(rs2.getString("creatorGroup"));	//創建人員角色
						trs.add(tr);
					}
					if (trs.size()>0) {
						mrBody.setTestResult(trs);	
					}
					
					/** 接著來檢查有沒有輸入過測試結果 **/
					wid.checkTRsubmittAble(tpid, mrid, ver, conn, ps, rs2);
					
				}
				
				/** 將原本送出的結果，放入結構 **/
				
				mrBodys.add(mrBody);
			}
			returnResult = new Gson().toJson(mrBodys);
			returnResult = returnResult.substring(1, returnResult.length());
			returnResult = "{\"sqlData\":["+returnResult+",\"strSQL\":\""+strSQL+"****"+tpid+"\",\"ver\":\""+ver+"\",\"size\":\""+mrBodys.size()+"\"}";
			
			/** 回傳SQL **/
			
		} catch (SQLException e) {
			returnResult = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e,"", true)+"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (rs2!= null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		}
		
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);		
	}
	
	
	private Require[] reqList(int tpid,String mrid,Connection conn,PreparedStatement ps,ResultSet rs) {
		Require[] reqList = null;
		
		String strSQL = "SELECT COUNT(*) FROM tms_mritem_requirelog WHERE tpid=? AND mrid=?";
		
		try {
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs = ps.executeQuery();
			
			rs.next();
			int count = rs.getInt(1); 
			if ( count !=0 ) {
				reqList = new Require[count];
				/**
				 * 測試項目的
				 * **/
				
				strSQL = "SELECT reqLogID,swdsID,sitrID FROM tms_mritem_requirelog WHERE tpid=? AND mrid=?";
				
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs = ps.executeQuery();
				int ind = 0;
				while (rs.next()){
					Require require = new Require();
					require.setReqLogID(rs.getString("reqLogID"));
					require.setSwdsID(rs.getString("swdsID"));
					require.setSitrID(rs.getString("sitrID"));
					reqList[ind]= require;
					ind++;
				}
			}
		} catch (SQLException e) {
			Widgets wid = new Widgets();
			wid.keepErrorMes(conn, ps, e,"", true);
		}
		return reqList;
	}
	
	private User[] erList(String type,int tpid,String mrid,Connection conn,PreparedStatement ps,ResultSet rs) {
		User[] erList = null;
		String strSQL = "SELECT COUNT(*) FROM tms_mritem_"+type+" WHERE tpid=? AND mrid=?";
		Widgets wid = new Widgets();
		try {
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs = ps.executeQuery();
			
			rs.next();
			int count = rs.getInt(1); 
			if ( count !=0 ) {
				erList = new User[count];
				
				/**
				 * 測試項目的
				 * **/
				
				strSQL = "SELECT tms_mritem_"+type+".userID, tms_personnel.nickName FROM tms_mritem_"+type+" left join tms_personnel on tms_mritem_"+type+".userID = tms_personnel.userID WHERE tms_mritem_"+type+".tpid=? AND tms_mritem_"+type+".mrid=?"; 
				
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				rs = ps.executeQuery();
				int ind = 0;
				while (rs.next()){
					erList[ind] = wid.checkUserId(rs.getString("userID"));
					ind++;
				}
			}
		} catch (SQLException e) {
			wid.keepErrorMes(conn, ps, e,"", true);
		}
		return erList;
	}
}
